package ch.sv7.tool.dbmanager.db.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import ch.sv7.tool.dbmanager.db.AbstractDatabaseSchemaFactory;
import ch.sv7.tool.dbmanager.db.ColumnSchema;
import ch.sv7.tool.dbmanager.db.ColumnType;
import ch.sv7.tool.dbmanager.db.ForeignKeySchema;
import ch.sv7.tool.dbmanager.db.IndexSchema;
import ch.sv7.tool.dbmanager.db.TableSchema;
import ch.sv7.tool.dbmanager.db.dialect.Dialect;
import ch.sv7.tool.dbmanager.db.dialect.DialectException;
import ch.sv7.tool.log.Logger;

public class SQLDatabaseSchemaFactory extends AbstractDatabaseSchemaFactory {

	private static final Logger log = Logger.getLogger(SQLDatabaseSchemaFactory.class);
	
	private Connection con;
	private String catalog;
	private String schema;	
	private Dialect dialect;

	public SQLDatabaseSchemaFactory(Connection con, String catalog, String schema, Dialect dialect) {
		this.con = con;
		this.catalog = catalog;
		this.schema = schema;
		this.dialect = dialect;
	}

	public SQLDatabaseSchemaFactory(Connection con, String schema, Dialect dialect) {
		this(con, null, schema, dialect);
	}

	public SQLDatabaseSchemaFactory(Connection con, Dialect dialect) {
		this(con, null, null, dialect);
	}
	
	public String[] getTables() {
		List tables = new ArrayList();
		ResultSet rs = null;
		try {
			DatabaseMetaData metaData = con.getMetaData();
			log.debug("Reading tables from database");
			rs = metaData.getTables(catalog, schema, null, new String[]{"TABLE"});
			while (rs.next()) {
				tables.add(rs.getString(3));
			}
			String[] result = (String[]) tables.toArray(new String[tables.size()]);
			log.debug("" + result.length + " tables found!");
			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return new String[0];
		} finally {
			try {
				if (rs != null){
					rs.close();
				}
			} catch (SQLException ignore) {
				return null;
			}
		}
	}

	private Set getAutoIncrementColumns(String tableName, Connection conn) throws SQLException{
		Set result = new HashSet();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM " + tableName + " WHERE 1=2");
			ResultSetMetaData rsMetaData = rs.getMetaData();
			for (int i = 1; i <= rsMetaData.getColumnCount(); i++){
				if (rsMetaData.isAutoIncrement(i)){
					result.add(rsMetaData.getColumnName(i));
				}
			}
			return result;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
		}
	}
	
	private long getRowNumber(String tableName, Connection conn) throws SQLException{
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT count(*) FROM " + tableName);
			if (rs.next()){
				return rs.getLong(1);
			}
			return -1;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
		}
	}
	
	public TableSchema getTableSchema(String name) {
		Map columnList = new HashMap();
		Map pkColumnList = new HashMap();
		TableSchema ts = new TableSchema(name, schema);
		ResultSet rs = null;
		try {
			long nbRows = getRowNumber(name, con);
			ts.setNbRows(nbRows);
			log.info("Reading table " + name + " (" + nbRows +" rows)");
			DatabaseMetaData metaData = con.getMetaData();
			String columnName;
			ColumnSchema cs;
			/*
			 * Loading columns
			 */
			try {
				
				Set autoIncrementedCols = getAutoIncrementColumns(name, con);
				
				for (rs = metaData.getColumns(catalog, schema, name, null); rs.next(); columnList.put(columnName, cs)) {
					columnName = rs.getString("COLUMN_NAME");
					int type = rs.getShort("DATA_TYPE");
					long size = rs.getLong("COLUMN_SIZE");
					long decimalSize = rs.getLong("DECIMAL_DIGITS");
					boolean nullable = rs.getBoolean("NULLABLE");
					String defaultValue = rs.getString("COLUMN_DEF");
					if (defaultValue != null){
						defaultValue = defaultValue.trim();
					}
					String typeName = rs.getString("TYPE_NAME");
					// does not work with sybase...
					//boolean autoIncrement = "YES".equals(rs.getString("IS_AUTOINCREMENT"));
					boolean autoIncrement = autoIncrementedCols.contains(columnName);
					if (typeName.endsWith(" identity")){ // TODO: is it too sybase specific ?
						autoIncrement = true;
						typeName = typeName.substring(0, typeName.length() - " identity".length());
					}
					ColumnType columnType = new ColumnType(type, size, decimalSize, nullable, autoIncrement, typeName, defaultValue);
					cs = new ColumnSchema(ts, columnName, columnType, false);
				}

				if (columnList.size() == 0) {
					return null;
				}
			} finally {
				if (rs != null) {
					rs.close();
					rs = null;
				}
			}
			
			/*
			 * Loading primary keys
			 */
			String pkName = null;
			try {			
				for (rs = metaData.getPrimaryKeys(catalog, schema, name); rs.next(); pkColumnList.put(columnName, cs)) {
					columnName = rs.getString("COLUMN_NAME");
					pkName = rs.getString("PK_NAME");
					cs = (ColumnSchema) columnList.get(columnName);
					cs.setPrimaryKey(true);
				}

			} finally {
				if (rs != null) {
					rs.close();
					rs = null;
				}
			}

			/*
			 * Register ColumnSchema objects into TableSchema
			 */
			for (Iterator iter = columnList.values().iterator(); iter.hasNext(); ts.addColumnSchema(cs))
				cs = (ColumnSchema) iter.next();

			
			/*
			 * Loading index info
			 */
			try {
				rs = metaData.getIndexInfo(catalog, schema, ts.getName(), false, false);
				while (rs.next()){
					boolean pkIndex = false;
					String indexName = rs.getString("INDEX_NAME");
					if (indexName != null && indexName.length() > 0){
						if (pkName != null && pkName.equals(indexName)){
							pkIndex = true;
						}
						ts.getIndexes().add(
								new IndexSchema(
										ts,
										indexName,
										!rs.getBoolean("NON_UNIQUE"),
										rs.getString("INDEX_QUALIFIER"),
										rs.getInt("TYPE"),
										rs.getString("COLUMN_NAME"),
										pkIndex));
					}
				}
			} finally {
				if (rs != null) {
					rs.close();
					rs = null;
				}
			}
			
			IndexSchema.mergeIndexes(ts.getIndexes());
			
			try {
				rs = metaData.getCrossReference(null, null, null, catalog, schema, ts.getName());
				while (rs.next()){
					String fkName = rs.getString("FK_NAME");
					String fkColumn = rs.getString("PKCOLUMN_NAME");
					String fkTable = rs.getString("PKTABLE_NAME");
					String pkColumn = rs.getString("FKCOLUMN_NAME");
					int updateRule = rs.getInt("UPDATE_RULE");
					int deleteRule = rs.getInt("DELETE_RULE");
					int deferrability = rs.getInt("DEFERRABILITY");
					if (fkName == null) {
						fkName = pkColumn + "-" + fkTable + "." + fkColumn;
					}
					ts.getForeignKeys().add(
							new ForeignKeySchema(
									ts,
									fkName,
									pkColumn,
									fkTable,
									fkColumn,
									updateRule,
									deleteRule,
									deferrability));
				}
			} finally {
				if (rs != null) {
					rs.close();
				}					
			}
			ForeignKeySchema.mergeForeignKeys(ts.getForeignKeys());
			
			if (dialect != null) {
				dialect.readDatabaseSpecificInformation(ts, con);
			}
			
			return ts;
		} catch (DialectException e) {
			e.printStackTrace();
			return null;
		} catch (SQLException sqlexception) {
			sqlexception.printStackTrace();
			return null;
		}
	}
}
